<?php
/* -------------------------------------------------------------------------------------
* 	ID:						$Id: class.products_filter.php 544 2015-02-28 12:00:26Z phone.mueller@googlemail.com $
* 	Letzter Stand:			$Revision: 544 $
* 	zuletzt geaendert von:	$Author: $
* 	Datum:					$Date: 2015-02-28 12:00:26 +0000 (Sat, 28 Feb 2015) $
*
* 	extends by dirk.mueller (edv-mueller.com)
* 	http://www.itdotmedia.de
*
* --------------------------------------------------------------------------------------
* 	based on:
* 	SEO:mercari by Siekiera Media
* 	http://www.seo-mercari.de
* 	Copyright (c) since 2011 SEO:mercari
* 	(c) 2000-2001 The Exchange Project  (earlier name of osCommerce)
* 	(c) 2002-2003 osCommerce - www.oscommerce.com
* 	(c) 2003     nextcommerce - www.nextcommerce.org
* 	(c) 2005     xt:Commerce - www.xt-commerce.com
*
* 	Released under the GNU General Public License
* --------------------------------------------------------------------------------------
* SQL - Help

select 
	tbl1.konfigurator_option, p.products_model, pd.products_name, p.products_status, tbl2.* 
from
	mercari_konfigurator_product_option tbl1
	inner join mercari_konfigurator_product_option tbl2 on tbl1.product_id = tbl2.product_id
	left join mercari_products p on tbl2.product_id = p.products_id
	left join mercari_products_description pd on tbl2.product_id = pd.products_id
where
	tbl1.konfigurator_option_group = 'MOEBELPROGRAMM' and tbl1.konfigurator_option like '%PRG_UPDATE-4%'
	and tbl2.konfigurator_option_group = 'FILTER_GESTELLART'

* ----------------------------------------------------------------------------------- */
require_once(DIR_FS_INC.'inc.parse_parameter_list.php');

class products_filter {
  var $db;

	var $activ = false;  
  var $price_min, $price_max;
  var $filters;
  var $filter_name;
  var $filter_type; // product, category
  
  var $products_id;
	var $_filter_groups;
	var $cat_url;
  
	function __construct() {
    global $db, $console;
    
    $this->db = $db;	 
    $this->products_id = array();
		$this->price_bounds = array('min' => 0, 'max' => 0);

		$_REQUEST = array_merge($_REQUEST, $_GET);
		$console->add($_SESSION['filter']);
		
	}

	function flat_sub_filter($req_filter) {
		if (empty($req_filter)) return array();

    $d = array(); // | rausnehmen    	 
    foreach($req_filter as $v) {
			foreach(explode('|', $v) as $item) {
				$d[] = $item;
			}
		}
    return array_filter($d);
	}
	
	function getCategoryUrl() {
		global $seo_url;
		if ($this->cat_url == '') {
			if(isset($_GET['cPath'])) {
				$cat_id = (explode('_', $_GET['cPath']));
				$cat_id = end($cat_id);
			}
			else
				$cat_id = '0';
			
			if ($cat_id > 0 && $seo_url) {
				$cat_url = $seo_url->getSiteLink('cPath', $cat_id);
				$d = explode('?', $cat_url);
				$this->cat_url = $d[0];
			}
		}
		return $this->cat_url;
	}
	
  function get_price_bounds($cat_id, $deep=false) {

		$sql = "
      select
      	min(p.products_price) _min, 
      	max(p.products_price) _max, 
      	min(p.products_price*(100.0+t.tax_rate)/100.0) _mintax, 
      	max(p.products_price*(100.0+t.tax_rate)/100.0) _maxtax 
      from
      	".TABLE_PRODUCTS_TO_CATEGORIES." ptc
      	inner join ".TABLE_PRODUCTS." p on p.products_id = ptc.products_id
      	inner join ".TABLE_TAX_RATES." t on t.tax_rates_id = p.products_tax_class_id
      where
      	p.products_status = 1
      	and ptc.categories_id = ".$cat_id."      
    ";
    $prices = $this->db->db_query($sql);
		if ($_SESSION['customers_status']['customers_status_show_price_tax'] == 0)
			return array('min' => floor($prices->fields['_min']), 'max' => ceil($prices->fields['_max']));
		else
			return array('min' => floor($prices->fields['_mintax']), 'max' => ceil($prices->fields['_maxtax']));
  }
  
  function set_filter_name() {
    $this->filter_name = array();

    if(
      (isset($_GET['filter']) && !empty($_GET['filter']))
	     || (isset($_GET['sub_filter']) && !empty($_GET['sub_filter']))
	     || isset($_GET['minp']))
    {

    	if(isset($_GET['minp']) || isset($_GET['maxp']))
        $this->filter_name[] = 'Preis: ' . $price->format($this->price_min, true)
                             . ' - ' . $price->format($this->price_max, true);
    }
    
  }

  function set_request() {
		list($req_filter, $req_sub_filter, $req_minp, $req_maxp) = $this->get_request();

		$_SESSION['product_filter']['cPath'] = $_GET['cPath'];
		$_SESSION['product_filter']['filter_type'] = $this->filter_type;
		$_SESSION['product_filter']['minp'] = $req_minp;
		$_SESSION['product_filter']['maxp'] = $req_maxp;
		$_SESSION['product_filter']['bminp'] = $this->price_bounds['min'];
		$_SESSION['product_filter']['bmaxp'] = $this->price_bounds['max'];
		$_SESSION['product_filter']['sub_filter'] = $req_sub_filter;
  }
  
  function get_filter_param() {
    $filter_param = array();
		
		$filter_param[] = 'filter_type='.$this->filter_type;

		list($req_filter, $req_sub_filter, $req_minp, $req_maxp) = $this->get_request();
		if(!empty($req_minp)) $filter_param[] = 'minp='.$req_minp;
		if(!empty($req_maxp)) $filter_param[] = 'maxp='.$req_maxp;
		if($this->price_bounds['min'] > 0) $filter_param[] = 'bminp='.$this->price_bounds['min'];
		if($this->price_bounds['max'] > 0) $filter_param[] = 'bmaxp='.$this->price_bounds['max'];
		if(!empty($req_sub_filter)) {
			foreach($req_sub_filter as $req_sub_filter_item) 
				$filter_param[] = 'sub_filter[]='.$req_sub_filter_item;
		}

		if(!empty($filter_param))
			$filter_param = '?' . explode('&', $filter_param);
		else
			$filter_param = '?filter=n';
  }

  function reset() {
		global $console;
		$this->activ = false;
		unset($_SESSION['product_filter']);
		$console->add('RESET FILTER');
	}
	
  function get_request() {
  	global $console, $tracking;
  	
		//echo_e($_GET);
		//echo_e($_POST);
		//echo_e($_SESSION['product_filter']);
		
		if ( !isset($_POST['action']) ){
			if (isset($_SESSION['product_filter'])) {
				$this->activ = true;
				$_REQUEST = array_merge($_SESSION['product_filter'], $_REQUEST);
			}
			else 
				$this->reset();
		}
		else {
			$this->activ = true;
		}
		
		//echo_e($_REQUEST);

		$req_filter = isset($_REQUEST['filter']) ? $_REQUEST['filter'] : '';
		$req_sub_filter = isset($_REQUEST['sub_filter']) ? $_REQUEST['sub_filter'] : '';
			$req_sub_filter = $this->flat_sub_filter($req_sub_filter);

		$this->filter_type = isset($_REQUEST['filter_type']) ? $_REQUEST['filter_type'] : 'product';
		$this->price_bounds['min'] = $_REQUEST['bminp'];
		$this->price_bounds['max'] = $_REQUEST['bmaxp'];

		$req_minp = isset($_REQUEST['minp']) ? $_REQUEST['minp'] : $this->price_bounds['min'];
		$req_maxp = isset($_REQUEST['maxp']) ? $_REQUEST['maxp'] : $this->price_bounds['max'];
		
		//echo_e(array($req_filter, $req_sub_filter, $req_minp, $req_maxp), 'e');
		return array($req_filter, $req_sub_filter, $req_minp, $req_maxp);
	}
  
  function get_products_id() {
    if (count($this->products_id) > 0) return $this->products_id;
		list($req_filter, $req_sub_filter, $req_minp, $req_maxp) = $this->get_request();
		//echo_e(array($req_filter, $req_sub_filter, $req_minp, $req_maxp));
		//echo_e($this->price_bounds);
		
		if ( 
				(empty($req_filter) || count($req_filter) == 0)
				&& (empty($req_sub_filter) || count($req_sub_filter) == 0)
				&& (empty($req_minp) || $req_minp == $this->price_bounds['min'])
				&& (empty($req_maxp) || $req_maxp == $this->price_bounds['max'])) {
			return false;
		}
			
		// price
		$price_bound = $this->db->db_query("select max(tbl.products_price)*2.0 _max from ".TABLE_PRODUCTS." tbl", true);
		$this->price_min = empty($req_minp) ? $this->price_bounds['min'] : $req_minp;
		$this->price_max = empty($req_maxp) ? $this->price_bounds['max'] : $req_maxp;
		
		if($this->filter_type == 'category') {
			// Kategorie kann auch OHNE Preis gefiltert werden!
			$this->price_min = $req_minp;
			$this->price_max = $req_maxp;
		}

		if ($_SESSION['customers_status']['customers_status_show_price_tax'] == 0)
			$sql_price = "p.products_price";
		else
			$sql_price = "p.products_price*(100+t.tax_rate)/100.0";
		
		if ( (empty($req_sub_filter) || count($req_sub_filter) == 0) ) {
			//just price
			$aSql['FILTER'] = "
				select
					p.products_id,
					".$sql_price." _price
				from
					".TABLE_PRODUCTS." p
					inner join ".TABLE_TAX_RATES." t on t.tax_rates_id = p.products_tax_class_id
				where
					".$sql_price." >= ".$this->price_min."
					and ".$sql_price." <= ".$this->price_max." 
				group by 
					p.products_id";
		}
		
		else {
			// have sub filters

			// create groups of filter for AND
			$filter_groups= array();
			if(!empty($req_sub_filter)) {
				$req_sub_filter = $this->flat_sub_filter($req_sub_filter);
				$this->filters = $req_sub_filter;
				$sql = "
						select
							concat('FILTER|', flt.parent_id) parent_id, 
							group_concat(flt.filter_id) _filters
						from
							".TABLE_PRODUCTS_FILTERS." flt
						where
							filter_id in ('".implode("', '", $req_sub_filter)."')
						group by
							flt.parent_id
					UNION
						select
							concat('OPTION|', flt.konfigurator_optiongroups_id) parent_id, 
							group_concat( flt.konfigurator_options_id) _filters
						from
							mercari_konfigurator_options flt
						where
							flt.konfigurator_options_id in ('".implode("', '", $req_sub_filter)."')
						group by
							flt.konfigurator_optiongroups_id";
				//echo_e($sql, 'e');
				$query = $this->db->db_query($sql, true);
				if ($query->_numOfRows) {
					while(!$query->EOF) {
						$p = explode('|', $query->fields['parent_id']);
						$filter_groups[$p[0]][$p[1]] = $query->fields['_filters'];
						$query->MoveNext();
					}
				}
				//echo_e($filter_groups);
			}

			if (isset($filter_groups['FILTER'])) {
				$aSql['FILTER'] = "
					select
						p.products_id,
						".$sql_price." _price,
						GROUP_CONCAT(ptf.filter_id) filter_list
					from
						".TABLE_PRODUCTS_TO_FILTERS." ptf
						inner join ".TABLE_PRODUCTS." p on p.products_id = ptf.products_id
						inner join ".TABLE_TAX_RATES." t on t.tax_rates_id = p.products_tax_class_id
					where
						1=1
						". (!empty($this->price_min) ? "and ".$sql_price." >= ".$this->price_min : "") . "
						". (!empty($this->price_max) ? "and ".$sql_price." <= ".$this->price_max : "") . " 
					group by 
						ptf.products_id
					having 
						1=1 ";

				foreach($filter_groups['FILTER'] as $g => $flts) {
					$arrFlt = array();
					foreach(explode(',', $flts) as $flt) $arrFlt[] = "find_in_set ('".$flt."', filter_list)";
					$aSql['FILTER'] .= "and (" . implode(" or ", $arrFlt) . ") ";
				}
			}

			if (isset($filter_groups['OPTION'])) {
				// add option paramerter
				$group_enable_url = $this->getCategoryUrl();
				//echo_e($group_enable_url);

				$sql = "
					SELECT 
						konfigurator_optiongroups_id,  konfigurator_optiongroups_param
					FROM 
						".TABLE_KONFIGURATOR_OPTIONGROUPS."
					WHERE
					konfigurator_optiongroups_id in ('".implode("', '", array_keys($filter_groups['OPTION'])) . "')";

				$query = $this->db->db_query($sql, true);
				$filter_groups_OPTION = array();
				while(!$query->EOF) {
					$id = $query->fields['konfigurator_optiongroups_id'];
					$p = parse_parameter_list($query->fields['konfigurator_optiongroups_param']);
					if (isset($p['group']) && $p['group'] == $group_enable_url)
						$filter_groups_OPTION[$p['group']][$id] = $filter_groups['OPTION'][$id];
					else
						$filter_groups_OPTION[] = array($id => $filter_groups['OPTION'][$id]);
					$query->moveNext();
				}
				$filter_groups['OPTION'] = $filter_groups_OPTION;
				$this->_filter_groups = $filter_groups;
			
				$aSql['OPTION'] = "
					select
						p.products_id,
						concat(',', group_concat(pto.konfigurator_option), ',') gr_konfigurator_option,
						".$sql_price." _price,
						'' filter_list
					from
						".TABLE_KONFIGURATOR_PRODUCT_OPTION." pto
						inner join ".TABLE_PRODUCTS." p on p.products_id = pto.product_id
						inner join ".TABLE_TAX_RATES." t on t.tax_rates_id = p.products_tax_class_id
					where
						1=1
						". (!empty($this->price_min) ? "and ".$sql_price." >= ".$this->price_min : "") . "
						". (!empty($this->price_max) ? "and ".$sql_price." <= ".$this->price_max : "") . " 
						and pto.konfigurator_option_group in ('%OPTIONGROUP_ID%')
					group by 
						pto.product_id
					having 
						1=1
						%OPTION_ID%
					";

				//build and/or groups
				//echo_e($filter_groups['OPTION'], 'e');
				foreach($filter_groups['OPTION'] as $filter_group_OPTION) {
					$filter_group_OPTION_items = array();
					foreach($filter_group_OPTION as $v) $filter_group_OPTION_items = array_merge($filter_group_OPTION_items, explode(',', $v));
					$_OPTION_ID = "(gr_konfigurator_option like '%," 
											. implode(",%' or gr_konfigurator_option like '%,", $filter_group_OPTION_items)
											. ",%')";
					$aSql['OPTION'] = str_replace("%OPTION_ID%", "and ".$_OPTION_ID." %OPTION_ID%", $aSql['OPTION']);
					foreach($filter_group_OPTION as $g => $flts) {
						$aSql['OPTION'] = str_replace("%OPTIONGROUP_ID%", $g."', '%OPTIONGROUP_ID%", $aSql['OPTION']);
					}
				}
				$aSql['OPTION'] = str_replace("%OPTION_ID%", "", $aSql['OPTION']);
				$aSql['OPTION'] = str_replace("%OPTIONGROUP_ID%", "DUMMY", $aSql['OPTION']);
			}
		}
			
		//echo_e($aSql);
		$this->products_id['FILTER'] = array();
		$this->products_id['OPTION'] = array();
		foreach($aSql as $k => $sql) {
			$product_ids_query = $this->db->db_query($sql, true);
			if($product_ids_query->_numOfRows) {
				while(!$product_ids_query->EOF) {
					$this->products_id[$k][] = $product_ids_query->fields['products_id'];
					$product_ids_query->MoveNext();
				}
			}
		}
		//echo_e($this->products_id);
		$this->products_id['all'] = array_unique(array_merge($this->products_id['FILTER'], $this->products_id['OPTION']));
		if (isset($filter_groups['FILTER']))
			$this->products_id['all'] = array_intersect($this->products_id['all'], $this->products_id['FILTER']);
		if (isset($filter_groups['OPTION']))
			$this->products_id['all'] = array_intersect($this->products_id['all'], $this->products_id['OPTION']);

		//echo_e($this->products_id);
		//echo_e(implode(', ', $this->products_id['all']), 'e');
		$this->products_id = $this->products_id['all'];
		if(empty($this->products_id) && $this->filter_type == 'category')
			return array('dummy');
		else
			return $this->products_id;

  }

	
	/** projekt stuff **/
	// filter programs with ALL the filtergroup Items
	function BM_get_programCategoriesFilter($filter_products) {
		if($this->filter_type == 'category' && !empty($filter_products)) {
			$sql = "
				select distinct
					kco.category_id, kco.konfigurator_option
				from
					".TABLE_KONFIGURATOR_PRODUCT_OPTION." kpo,
					".TABLE_KONFIGURATOR_CATEGORY_OPTION." kco
				where
					kpo.konfigurator_option_group = 'MOEBELPROGRAMM'
					and (kco.konfigurator_option_group = 'MOEBELPROGRAMM' and kpo.konfigurator_option  like concat('%', kco.konfigurator_option, '%'))
					".str_replace('p.products_id', 'kpo.product_id', $filter_products);
			//echo_e($sql);
			$rec = $this->db->db_query($sql, false);
			if($rec->_numOfRows) {
				$filter_categories = array();
				while(!$rec->EOF) {
					$filter_categories[] = array('ID' => $rec->fields['category_id'], 'NAME' =>  $rec->fields['konfigurator_option']);
					$rec->MoveNext();
				}
				//echo_e($filter_categories);
				//echo_e($this->_filter_groups);
				
				$sql_konfigurator_option_group = array();
				$sql_konfigurator_option = array();
				foreach($this->_filter_groups as $filter_group_OPTION) {
					foreach($filter_group_OPTION as $filter_OPTION) {
						foreach($filter_OPTION as $k => $v) {
							$sql_konfigurator_option_group[] = $k;
							$sql_konfigurator_option = array_merge($sql_konfigurator_option, explode(',', $v));
						}
					}
				}
				//echo_e(array($sql_konfigurator_option_group, $sql_konfigurator_option));
				
				$a = array();
				foreach($filter_categories as $filter_category) {
					$sql = "
						select distinct
							pto2.konfigurator_option_group
						from
							".TABLE_KONFIGURATOR_PRODUCT_OPTION." pto1
							inner join ".TABLE_KONFIGURATOR_PRODUCT_OPTION." pto2 on pto1.product_id = pto2.product_id
						where
							pto1.konfigurator_option_group = 'MOEBELPROGRAMM' 
							and pto1.konfigurator_option  like concat('%', '".$filter_category['NAME']."', '%')
							and pto2.konfigurator_option_group in ('".implode("', '", $sql_konfigurator_option_group)."')
							and pto2.konfigurator_option in ('".implode("', '", $sql_konfigurator_option)."')";
						
					//echo_e($sql);
					$rec = $this->db->db_query($sql, false);
					if($rec->_numOfRows == count($sql_konfigurator_option_group)) {
						$a[] = $filter_category['ID'];
					}
				}
				$filter_categories = $a;
				//echo_e($filter_categories);

				$filter_categories = "and c.categories_id in('" . implode("', '", $filter_categories) . "') ";
			}
			else
				$filter_categories = "and c.categories_id in('noshow') ";
			
			return $filter_categories;
		}
		else {
			return "";
		}
	}
}
?>